﻿using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Dapper.Sharding
{
    public abstract class IUnion
    {
        protected IDatabase db;
        protected int skip;
        protected int take;
        object par;
        DistributedTransaction tran;
        int? timeout;
        protected StringBuilder sqlTable = new();
        protected string sqlWhere;
        protected StringBuilder sbWhere;
        protected string sqlGroupBy;
        protected string sqlHaving;
        protected string sqlOrderBy;
        protected string returnFields;
        protected string distinct;
        private CancellationToken token = default;

        public IUnion(IDatabase db)
        {
            this.db = db;
        }

        public IUnion(IDatabase db, string sql)
        {
            this.db = db;

            if (db.DbType == DataBaseType.Oracle || db.DbType == DataBaseType.Sqlite)
            {
                sqlTable.Append("SELECT * FROM (");
                sqlTable.Append(sql);
                sqlTable.Append(')');
            }
            else
            {
                sqlTable.Append('(');
                sqlTable.Append(sql);
                sqlTable.Append(')');
            }
        }

        public IUnion Union(IQuery query)
        {
            if (sqlTable.Length == 0)
            {
                if (db.DbType == DataBaseType.Oracle || db.DbType == DataBaseType.Sqlite)
                {
                    sqlTable.Append("SELECT * FROM (");
                    sqlTable.Append(query.Sql());
                    sqlTable.Append(')');
                }
                else
                {
                    sqlTable.Append('(');
                    sqlTable.Append(query.Sql());
                    sqlTable.Append(')');
                }
            }
            else
            {
                if (db.DbType == DataBaseType.Oracle || db.DbType == DataBaseType.Sqlite)
                {
                    sqlTable.Append(" UNION SELECT * FROM (");
                    sqlTable.Append(query.Sql());
                    sqlTable.Append(')');
                }
                else
                {
                    sqlTable.Append(" UNION (");
                    sqlTable.Append(query.Sql());
                    sqlTable.Append(')');
                }
            }
            return this;
        }

        public IUnion UnionAll(IQuery query)
        {
            if (sqlTable.Length == 0)
            {
                if (db.DbType == DataBaseType.Oracle || db.DbType == DataBaseType.Sqlite)
                {
                    sqlTable.Append("SELECT * FROM (");
                    sqlTable.Append(query.Sql());
                    sqlTable.Append(')');
                }
                else
                {
                    sqlTable.Append('(');
                    sqlTable.Append(query.Sql());
                    sqlTable.Append(')');
                }
            }
            else
            {
                if (db.DbType == DataBaseType.Oracle || db.DbType == DataBaseType.Sqlite)
                {
                    sqlTable.Append(" UNION ALL SELECT * FROM (");
                    sqlTable.Append(query.Sql());
                    sqlTable.Append(')');
                }
                else
                {
                    sqlTable.Append(" UNION ALL (");
                    sqlTable.Append(query.Sql());
                    sqlTable.Append(')');
                }
            }
            return this;
        }

        public IUnion Union(string sql)
        {
            if (sqlTable.Length == 0)
            {
                if (db.DbType == DataBaseType.Oracle || db.DbType == DataBaseType.Sqlite)
                {
                    sqlTable.Append("SELECT * FROM (");
                    sqlTable.Append(sql);
                    sqlTable.Append(')');
                }
                else
                {
                    sqlTable.Append('(');
                    sqlTable.Append(sql);
                    sqlTable.Append(')');
                }
            }
            else
            {
                if (db.DbType == DataBaseType.Oracle || db.DbType == DataBaseType.Sqlite)
                {
                    sqlTable.Append(" UNION SELECT * FROM (");
                    sqlTable.Append(sql);
                    sqlTable.Append(')');
                }
                else
                {
                    sqlTable.Append(" UNION (");
                    sqlTable.Append(sql);
                    sqlTable.Append(')');
                }
            }
            return this;
        }

        public IUnion UnionAll(string sql)
        {
            if (sqlTable.Length == 0)
            {
                if (db.DbType == DataBaseType.Oracle || db.DbType == DataBaseType.Sqlite)
                {
                    sqlTable.Append("SELECT * FROM (");
                    sqlTable.Append(sql);
                    sqlTable.Append(')');
                }
                else
                {
                    sqlTable.Append('(');
                    sqlTable.Append(sql);
                    sqlTable.Append(')');
                }
            }
            else
            {
                if (db.DbType == DataBaseType.Oracle || db.DbType == DataBaseType.Sqlite)
                {
                    sqlTable.Append(" UNION ALL SELECT * FROM (");
                    sqlTable.Append(sql);
                    sqlTable.Append(')');
                }
                else
                {
                    sqlTable.Append(" UNION ALL (");
                    sqlTable.Append(sql);
                    sqlTable.Append(')');
                }
            }
            return this;
        }

        #region Abstract

        public abstract string Sql();

        public abstract string SqlCount();

        public abstract string SqlExists();

        public abstract string SqlMin(string field);

        public abstract string SqlMax(string field);

        public abstract string SqlSum(string field);

        public abstract string SqlAvg(string field);

        public string SqlSubQuery(string name)
        {
            return $"({Sql()}) {name}";
        }

        public string SqlSubQueryCount(string asName)
        {
            return $"({SqlCount()}) {asName}";
        }

        public string SqlSubQueryMin(string filed, string asName)
        {
            return $"({SqlMin(filed)}) {asName}";
        }

        public string SqlSubQueryMax(string filed, string asName)
        {
            return $"({SqlMax(filed)}) {asName}";
        }

        public string SqlSubQuerySum(string filed, string asName)
        {
            return $"({SqlSum(filed)}) {asName}";
        }

        public string SqlSubQueryAvg(string filed, string asName)
        {
            return $"({SqlAvg(filed)}) {asName}";
        }

        #endregion

        #region Query Condition

        public IUnion Where(string where)
        {
            if (string.IsNullOrEmpty(where))
            {
                sqlWhere = null;
            }
            else
            {
                sqlWhere = " WHERE " + where;
            }

            return this;
        }

        public IUnion WhereAnd(string and)
        {
            if (string.IsNullOrEmpty(and))
            {
                sbWhere?.Clear();
            }
            else
            {
                sbWhere ??= new();
                if (string.IsNullOrEmpty(sqlWhere))
                {
                    sqlWhere = " WHERE 1=1";
                }

                sbWhere.Append(" AND ");
                sbWhere.Append(and);
            }

            return this;
        }

        public IUnion WhereAndExists(IQuery q)
        {
            WhereAnd($"EXISTS({q.SqlExists()})");
            return this;
        }

        public IUnion WhereAndExists(string sql)
        {
            WhereAnd($"EXISTS({sql})");
            return this;
        }

        public IUnion GroupBy(string groupBy)
        {
            if (string.IsNullOrEmpty(groupBy))
            {
                sqlGroupBy = null;
            }
            else
            {
                sqlGroupBy = " GROUP BY " + groupBy;
            }

            return this;
        }

        public IUnion Having(string having)
        {
            if (string.IsNullOrEmpty(having))
            {
                sqlHaving = null;
            }
            else
            {
                sqlHaving = " HAVING " + having;
            }

            return this;
        }

        public IUnion OrderBy(string orderBy)
        {
            if (string.IsNullOrEmpty(orderBy))
            {
                sqlOrderBy = null;
            }
            else
            {
                sqlOrderBy = " ORDER BY " + orderBy;
            }

            return this;
        }

        public IUnion Distinct(bool set = true)
        {
            if (set)
            {
                distinct = " DISTINCT";
            }
            else
            {
                distinct = null;
            }

            return this;
        }

        public IUnion ReturnFields(string fields)
        {
            returnFields = fields;
            return this;
        }

        public IUnion Limit(int count)
        {
            skip = 0;
            take = count;
            return this;
        }

        public IUnion Limit(int skip, int take)
        {
            this.skip = skip;
            this.take = take;
            return this;
        }

        public IUnion Page(int page, int pageSize)
        {
            int skip = 0;
            if (page > 1)
            {
                skip = (page - 1) * pageSize;
            }
            this.skip = skip;
            take = pageSize;
            return this;
        }

        public IUnion Param(object param)
        {
            par = param;
            return this;
        }

        public IUnion Transaction(DistributedTransaction tran)
        {
            this.tran = tran;
            return this;
        }

        public IUnion CancellationToken(CancellationToken token)
        {
            this.token = token;
            return this;
        }

        public IUnion Timeout(int? timeout = null)
        {
            this.timeout = timeout;
            return this;
        }

        public void Clear()
        {
            sqlWhere = null;
            sbWhere?.Clear();
            sqlGroupBy = null;
            sqlHaving = null;
            sqlOrderBy = null;
            returnFields = null;
            distinct = null;
            skip = 0;
            take = 0;
        }

        #endregion

        #region Query

        public long Count()
        {
            return db.ExecuteScalar<long>(SqlCount(), par, tran, timeout);
        }

        public Task<long> CountAsync()
        {
            return db.ExecuteScalarAsync<long>(SqlCount(), par, tran, timeout, token);
        }

        public T QueryFirstOrDefault<T>()
        {
            return db.QueryFirstOrDefault<T>(Sql(), par, tran, timeout);
        }

        public Task<T> QueryFirstOrDefaultAsync<T>()
        {
            return db.QueryFirstOrDefaultAsync<T>(Sql(), par, tran, timeout, token);
        }

        public dynamic QueryFirstOrDefault()
        {
            return db.QueryFirstOrDefault(Sql(), par, tran, timeout);
        }

        public Task<dynamic> QueryFirstOrDefaultAsync()
        {
            return db.QueryFirstOrDefaultAsync(Sql(), par, tran, timeout, token);
        }

        public List<T> Query<T>()
        {
            return db.Query<T>(Sql(), par, tran, timeout);
        }

        public Task<List<T>> QueryAsync<T>()
        {
            return db.QueryAsync<T>(Sql(), par, tran, timeout, token);
        }

        public List<dynamic> Query()
        {
            return db.Query(Sql(), par, tran, timeout);
        }

        public Task<List<dynamic>> QueryAsync()
        {
            return db.QueryAsync(Sql(), par, tran, timeout, token);
        }

        public DataTable QueryDataTable()
        {
            return db.QueryDataTable(Sql(), par, tran, timeout);
        }

        public Task<DataTable> QueryDataTableAsync()
        {
            return db.QueryDataTableAsync(Sql(), par, tran, timeout, token);
        }

        public T ExecuteScalar<T>()
        {
            return db.ExecuteScalar<T>(Sql(), par, tran, timeout);
        }

        public Task<T> ExecuteScalarAsync<T>()
        {
            return db.ExecuteScalarAsync<T>(Sql(), par, tran, timeout, token);
        }

        public object ExecuteScalar()
        {
            return db.ExecuteScalar(Sql(), par, tran, timeout);
        }

        public Task<object> ExecuteScalarAsync()
        {
            return db.ExecuteScalarAsync(Sql(), par, tran, timeout, token);
        }


        public IAsyncEnumerable<T> QueryUnbufferedAsync<T>()
        {
            return db.QueryUnbufferedAsync<T>(Sql(), par, tran, timeout, token);
        }

        public IAsyncEnumerable<dynamic> QueryUnbufferedAsync()
        {
            return db.QueryUnbufferedAsync(Sql(), par, tran, timeout, token);
        }


        /**********QueryPageAndCount************/

        public PageEntity<T> QueryPageAndCount<T>()
        {
            return new PageEntity<T>
            {
                Data = Query<T>(),
                Count = Count()
            };
        }

        public async Task<PageEntity<T>> QueryPageAndCountAsync<T>()
        {
            var task1 = QueryAsync<T>();
            var task2 = CountAsync();
            await Task.WhenAll(task1, task2);
            return new PageEntity<T>
            {
                Data = task1.Result,
                Count = task2.Result
            };
        }

        public PageEntity<dynamic> QueryPageAndCount()
        {
            return new PageEntity<dynamic>
            {
                Data = Query(),
                Count = Count()
            };
        }

        public async Task<PageEntity<dynamic>> QueryPageAndCountAsync()
        {
            var task1 = QueryAsync();
            var task2 = CountAsync();
            await Task.WhenAll(task1, task2);
            return new PageEntity<dynamic>
            {
                Data = task1.Result,
                Count = task2.Result
            };
        }

        public async Task<PageEntityUnbuffered<T>> QueryPageAndCountUnbuffered<T>()
        {
            var count = await CountAsync();
            return new PageEntityUnbuffered<T>
            {
                Data = QueryUnbufferedAsync<T>(),
                Count = count
            };
        }

        public async Task<PageEntityUnbuffered<dynamic>> QueryPageAndCountUnbuffered()
        {
            var count = await CountAsync();
            return new PageEntityUnbuffered<dynamic>
            {
                Data = QueryUnbufferedAsync(),
                Count = count
            };
        }

        #endregion

        #region aggregate

        public bool Exists()
        {
            return db.ExecuteScalar<bool>(SqlExists(), par, tran, timeout);
        }

        public async Task<bool> ExistsAsync()
        {
            return await db.ExecuteScalarAsync<bool>(SqlExists(), par, tran, timeout, token);
        }

        public TValue Min<TValue>(string field)
        {
            return db.ExecuteScalar<TValue>(SqlMin(field), par, tran, timeout);
        }

        public async Task<TValue> MinAsync<TValue>(string field)
        {
            return await db.ExecuteScalarAsync<TValue>(SqlMin(field), par, tran, timeout, token);
        }

        public TValue Max<TValue>(string field)
        {
            return db.ExecuteScalar<TValue>(SqlMax(field), par, tran, timeout);
        }

        public async Task<TValue> MaxAsync<TValue>(string field)
        {
            return await db.ExecuteScalarAsync<TValue>(SqlMax(field), par, tran, timeout, token);
        }

        public TValue Sum<TValue>(string field)
        {
            return db.ExecuteScalar<TValue>(SqlSum(field), par, tran, timeout);
        }

        public async Task<TValue> SumAsync<TValue>(string field)
        {
            return await db.ExecuteScalarAsync<TValue>(SqlSum(field), par, tran, timeout, token);
        }

        public decimal Avg(string field)
        {
            if (db.DbType != DataBaseType.ClickHouse)
            {
                return db.ExecuteScalar<decimal>(SqlAvg(field), par, tran, timeout);
            }

            try
            {
                return db.ExecuteScalar<decimal>(SqlAvg(field), par, tran, timeout);
            }
            catch 
            {
                return 0;
            }
        }

        public async Task<decimal> AvgAsync(string field)
        {
            if (db.DbType != DataBaseType.ClickHouse)
            {
                return await db.ExecuteScalarAsync<decimal>(SqlAvg(field), par, tran, timeout, token);
            }

            try
            {
                return await db.ExecuteScalarAsync<decimal>(SqlAvg(field), par, tran, timeout, token);
            }
            catch
            {
                return 0;
            }
        }

        #endregion

    }
}
